﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Koonsoft.CustomerService.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;

namespace Koonsoft.CustomerService.DAL
{
    public class IssuesDAL
    {
        private Database database = DatabaseFactory.CreateDatabase("CustomerService");

        public bool Update(Issue issue, out string msg)
        {
            msg = "";

            const string sql = @"   update [Issues] set [Status]=@Status,[LastTime]=GetDate() where [IssueID] = @IssueID";

            try
            {
                using (var cmd = database.GetSqlStringCommand(sql))
                {
                    database.AddInParameter(cmd, "@IssueID", DbType.Guid, issue.IssueID);
                    database.AddInParameter(cmd, "@Status", DbType.Int32, issue.Status);
                    return database.ExecuteNonQuery(cmd) > 0;
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + ";" + ex.StackTrace;
            }
            return false;
        }

        public bool Create(Issue issue, out string msg)
        {
            msg = "";

            const string sql = @"insert into [Issues]([IssueID],[IssueTitle],[IssueLevel],[IssueCategory],[IssueSource],[ClientVersion],[CustomersQQ]
                                                        ,[CustomersMobile],[ServiceArea],[Region],[BandWidth],[SystemEnvironment],[DecodingRate]
                                                        ,[IsUnblocked],[Mark],[Status],[CreateUser],[ExceptionTime]
                                                        ,[UserName],[UserAccounts],[GSID],[ID],[PrincipalID],[OperatorsId])
                                    values(@IssueID,@IssueTitle,@IssueLevel,@IssueCategory,@IssueSource,@ClientVersion,@CustomersQQ
                                            ,@CustomersMobile,@ServiceArea,@Region,@BandWidth,@SystemEnvironment,@DecodingRate
                                            ,@IsUnblocked,@Mark,@Status,@CreateUser,@ExceptionTime
                                            ,@UserName,@UserAccounts,@GSID,@ID,@PrincipalID,@OperatorsId)";

            try
            {
                using (var cmd = database.GetSqlStringCommand(sql))
                {
                    database.AddInParameter(cmd, "@IssueID", DbType.Guid, issue.IssueID);
                    database.AddInParameter(cmd, "@IssueTitle", DbType.String, issue.IssueTitle);
                    database.AddInParameter(cmd, "@IssueLevel", DbType.String, issue.IssueLevel);
                    database.AddInParameter(cmd, "@IssueCategory", DbType.String, issue.IssueCategory);
                    database.AddInParameter(cmd, "@IssueSource", DbType.String, issue.IssueSource);
                    database.AddInParameter(cmd, "@ClientVersion", DbType.String, issue.ClientVersion);
                    database.AddInParameter(cmd, "@CustomersQQ", DbType.String, issue.CustomersQQ);
                    database.AddInParameter(cmd, "@CustomersMobile", DbType.String, issue.CustomersMobile);
                    database.AddInParameter(cmd, "@ServiceArea", DbType.String, issue.ServiceArea);
                    database.AddInParameter(cmd, "@Region", DbType.String, issue.Region);
                    database.AddInParameter(cmd, "@BandWidth", DbType.String, issue.BandWidth);
                    database.AddInParameter(cmd, "@SystemEnvironment", DbType.String, issue.SystemEnvironment);
                    database.AddInParameter(cmd, "@DecodingRate", DbType.String, issue.DecodingRate);
                    database.AddInParameter(cmd, "@IsUnblocked", DbType.Boolean, issue.IsUnblocked);
                    database.AddInParameter(cmd, "@Mark", DbType.String, issue.Mark);
                    database.AddInParameter(cmd, "@Status", DbType.Int32, issue.Status);
                    database.AddInParameter(cmd, "@CreateUser", DbType.String, issue.CreateUser);
                    database.AddInParameter(cmd, "@ExceptionTime", DbType.DateTime, DateTime.Now);
                    database.AddInParameter(cmd, "@UserName", DbType.String, issue.UserName);
                    database.AddInParameter(cmd, "@UserAccounts", DbType.String, issue.UserAccounts);
                    database.AddInParameter(cmd, "@GSID", DbType.String, issue.GSID);
                    database.AddInParameter(cmd, "@ID", DbType.String, issue.ID);
                    database.AddInParameter(cmd, "@PrincipalID", DbType.String, issue.PrincipalID);
                    database.AddInParameter(cmd, "@OperatorsId", DbType.String, issue.OperatorsId);
                    return database.ExecuteNonQuery(cmd) > 0;
                    // return DataTableToEmpolyees(database.ExecuteDataSet(cmd).Tables[0]);
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + ";" + ex.StackTrace;
            }
            return false;
        }

        public bool IssuesUpdate(Issue issue, out string msg)
        {
            msg = "";

            const string sql = @"update Issues set IssueTitle =@IssueTitle,IssueLevel=@IssueLevel,IssueCategory=@IssueCategory,
				                                    IssueSource=@IssueSource,ClientVersion=@ClientVersion,CustomersQQ=@CustomersQQ,
				                                    CustomersMobile=@CustomersMobile,ServiceArea=@ServiceArea,Region=@Region,
				                                    BandWidth=@BandWidth,SystemEnvironment=@SystemEnvironment,DecodingRate=@DecodingRate,
				                                    IsUnblocked=@IsUnblocked,Mark=@Mark,[Status]=@Status,CreateUser=@CreateUser,
				                                    ExceptionTime=@ExceptionTime,UserName=@UserName,UserAccounts=@UserAccounts,
				                                    GSID=@GSID,ID=@ID,PrincipalID=@PrincipalID,OperatorsId=@OperatorsId
	                                    where IssueID=@IssueID";

            try
            {
                using (var cmd = database.GetSqlStringCommand(sql))
                {
                    database.AddInParameter(cmd, "@IssueID", DbType.Guid, issue.IssueID);
                    database.AddInParameter(cmd, "@IssueTitle", DbType.String, issue.IssueTitle);
                    database.AddInParameter(cmd, "@IssueLevel", DbType.String, issue.IssueLevel);
                    database.AddInParameter(cmd, "@IssueCategory", DbType.String, issue.IssueCategory);
                    database.AddInParameter(cmd, "@IssueSource", DbType.String, issue.IssueSource);
                    database.AddInParameter(cmd, "@ClientVersion", DbType.String, issue.ClientVersion);
                    database.AddInParameter(cmd, "@CustomersQQ", DbType.String, issue.CustomersQQ);
                    database.AddInParameter(cmd, "@CustomersMobile", DbType.String, issue.CustomersMobile);
                    database.AddInParameter(cmd, "@ServiceArea", DbType.String, issue.ServiceArea);
                    database.AddInParameter(cmd, "@Region", DbType.String, issue.Region);
                    database.AddInParameter(cmd, "@BandWidth", DbType.String, issue.BandWidth);
                    database.AddInParameter(cmd, "@SystemEnvironment", DbType.String, issue.SystemEnvironment);
                    database.AddInParameter(cmd, "@DecodingRate", DbType.String, issue.DecodingRate);
                    database.AddInParameter(cmd, "@IsUnblocked", DbType.Boolean, issue.IsUnblocked);
                    database.AddInParameter(cmd, "@Mark", DbType.String, issue.Mark);
                    database.AddInParameter(cmd, "@Status", DbType.Int32, issue.Status);
                    database.AddInParameter(cmd, "@CreateUser", DbType.String, issue.CreateUser);
                    database.AddInParameter(cmd, "@ExceptionTime", DbType.DateTime, DateTime.Now);
                    database.AddInParameter(cmd, "@UserName", DbType.String, issue.UserName);
                    database.AddInParameter(cmd, "@UserAccounts", DbType.String, issue.UserAccounts);
                    database.AddInParameter(cmd, "@GSID", DbType.String, issue.GSID);
                    database.AddInParameter(cmd, "@ID", DbType.String, issue.ID);
                    database.AddInParameter(cmd, "@PrincipalID", DbType.String, issue.PrincipalID);
                    database.AddInParameter(cmd, "@OperatorsId", DbType.String, issue.OperatorsId);
                    return database.ExecuteNonQuery(cmd) > 0;
                    // return DataTableToEmpolyees(database.ExecuteDataSet(cmd).Tables[0]);
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + ";" + ex.StackTrace;
            }
            return false;
        }


        public List<Issue> GetIssues(Issue issue, out string msg, DateTime dtSatrt, DateTime dtEnd)
        {
            msg = "";

            DataTable dt = Get(issue, GetSelectSql(issue, dtSatrt, dtEnd), out msg, dtSatrt, dtEnd);
            if (dt == null || !string.IsNullOrEmpty(msg))
            {
                return new List<Issue>();
            }

            return DataTableToEmpolyees(dt);

        }

        public Issue Get(Guid issueId)
        {
            string sql = "select * From Issues where IssueID =@IssueID";
            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@IssueID", DbType.Guid, issueId);
                DataTable dt = database.ExecuteDataSet(cmd).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[0];
                    Issue issue = new Issue();

                    issue.IssueID = Guid.Parse(dr["IssueID"].ToString());
                    issue.BandWidth = dr["BandWidth"].ToString();
                    issue.IssueTitle = dr["IssueTitle"].ToString();
                    issue.ClientVersion = dr["ClientVersion"].ToString();
                    issue.CreateUser = dr["CreateUser"].ToString();
                    issue.CustomersMobile = dr["CustomersMobile"].ToString();
                    issue.CustomersQQ = dr["CustomersQQ"].ToString();
                    issue.DecodingRate = dr["DecodingRate"].ToString();
                    issue.ExceptionTime = DateTime.Parse(dr["ExceptionTime"].ToString());
                    issue.LastTime = DateTime.Parse(dr["LastTime"].ToString());
                    issue.IssueCategory = dr["IssueCategory"].ToString();
                    issue.IssueLevel = dr["IssueLevel"].ToString();
                    issue.IssueSource = dr["IssueSource"].ToString();
                    issue.IsUnblocked = bool.Parse(dr["IsUnblocked"].ToString());
                    issue.Mark = dr["Mark"].ToString();
                    issue.Region = dr["Region"].ToString();
                    issue.ServiceArea = dr["ServiceArea"].ToString();
                    issue.Status = int.Parse(dr["Status"].ToString());
                    issue.SystemEnvironment = dr["SystemEnvironment"].ToString();


                    issue.UserName = dr["UserName"].ToString();
                    issue.UserAccounts = dr["UserAccounts"].ToString();
                    issue.GSID = dr["Gsid"].ToString();
                    issue.ID = dr["Id"].ToString();
                    issue.PrincipalID = dr["Principalid"].ToString();
                    issue.OperatorsId = dr["operatorsId"].ToString();

                    return issue;
                }
            }
            return null;
        }

        private List<Issue> DataTableToEmpolyees(DataTable dt)
        {
            List<Issue> issues = new List<Issue>();
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Issue issue = new Issue();

                    issue.IssueID = Guid.Parse(dr["IssueID"].ToString());
                    issue.BandWidth = dr["BandWidth"].ToString();
                    issue.IssueTitle = dr["IssueTitle"].ToString();
                    issue.ClientVersion = dr["ClientVersion"].ToString();
                    issue.CreateUser = dr["CreateUser"].ToString();
                    issue.CustomersMobile = dr["CustomersMobile"].ToString();
                    issue.CustomersQQ = dr["CustomersQQ"].ToString();
                    issue.DecodingRate = dr["DecodingRate"].ToString();
                    issue.ExceptionTime = DateTime.Parse(dr["ExceptionTime"].ToString());
                    issue.LastTime = DateTime.Parse(dr["LastTime"].ToString());
                    issue.IssueCategory = dr["IssueCategory"].ToString();
                    issue.IssueLevel = dr["IssueLevel"].ToString();
                    issue.IssueSource = dr["IssueSource"].ToString();
                    issue.IsUnblocked = bool.Parse(dr["IsUnblocked"].ToString());
                    issue.Mark = dr["Mark"].ToString();
                    issue.Region = dr["Region"].ToString();
                    issue.ServiceArea = dr["ServiceArea"].ToString();
                    issue.Status = int.Parse(dr["Status"].ToString());
                    issue.SystemEnvironment = dr["SystemEnvironment"].ToString();


                    issue.UserName = dr["UserName"].ToString();
                    issue.UserAccounts = dr["UserAccounts"].ToString();
                    issue.GSID = dr["Gsid"].ToString();
                    issue.ID = dr["Id"].ToString();
                    issue.PrincipalID = dr["Principalid"].ToString();
                    issue.OperatorsId = dr["operatorsId"].ToString();

                    issues.Add(issue);
                }

                return issues;

            }

            return new List<Issue>();
        }

        private DataTable Get(Issue issue, string sql, out string msg, DateTime dtStart, DateTime dtEnd)
        {
            msg = "";

            try
            {
                using (var cmd = database.GetSqlStringCommand(sql))
                {

                    if (dtStart != new DateTime())
                    {
                        database.AddInParameter(cmd, "@dtStart", DbType.DateTime, dtStart.ToString("yyyy-MM-dd"));
                    }

                    if (dtEnd != new DateTime())
                    {

                        database.AddInParameter(cmd, "@dtEnd", DbType.DateTime, dtEnd.AddDays(1).ToString("yyyy-MM-dd"));
                    }

                    if (issue.IssueID != Guid.Empty)
                    {
                        database.AddInParameter(cmd, "@IssueID", DbType.Guid, issue.IssueID);
                    }

                    if (!string.IsNullOrEmpty(issue.IssueTitle))
                    {
                        database.AddInParameter(cmd, "@IssueTitle", DbType.String, issue.IssueTitle);
                    }

                    if (!string.IsNullOrEmpty(issue.BandWidth))
                    {
                        database.AddInParameter(cmd, "@BandWidth", DbType.String, issue.BandWidth);
                    }

                    if (!string.IsNullOrEmpty(issue.ClientVersion))
                    {
                        database.AddInParameter(cmd, "@ClientVersion", DbType.String, issue.ClientVersion);
                    }

                    if (!string.IsNullOrEmpty(issue.CreateUser))
                    {
                        database.AddInParameter(cmd, "@CreateUser", DbType.String, issue.CreateUser);
                    }

                    if (!string.IsNullOrEmpty(issue.CustomersMobile))
                    {
                        database.AddInParameter(cmd, "@CustomersMobile", DbType.String, issue.CustomersMobile);
                    }

                    if (!string.IsNullOrEmpty(issue.CustomersQQ))
                    {
                        database.AddInParameter(cmd, "@CustomersQQ", DbType.String, issue.CustomersQQ);
                    }

                    if (!string.IsNullOrEmpty(issue.DecodingRate))
                    {
                        database.AddInParameter(cmd, "@DecodingRate", DbType.String, issue.DecodingRate);
                    }

                    if (issue.ExceptionTime != null && issue.ExceptionTime != new DateTime())
                    {
                        database.AddInParameter(cmd, "@ExceptionTime", DbType.DateTime, issue.ExceptionTime);
                    }

                    if (!string.IsNullOrEmpty(issue.IssueCategory))
                    {
                        database.AddInParameter(cmd, "@IssueCategory", DbType.String, issue.IssueCategory);
                    }

                    if (!string.IsNullOrEmpty(issue.IssueLevel))
                    {
                        database.AddInParameter(cmd, "@IssueLevel", DbType.String, issue.IssueLevel);
                    }

                    if (!string.IsNullOrEmpty(issue.IssueSource))
                    {
                        database.AddInParameter(cmd, "@IssueSource", DbType.String, issue.IssueSource);
                    }

                    if (!string.IsNullOrEmpty(issue.Region))
                    {
                        database.AddInParameter(cmd, "@Region", DbType.String, issue.Region);
                    }

                    if (!string.IsNullOrEmpty(issue.ServiceArea))
                    {
                        database.AddInParameter(cmd, "@ServiceArea", DbType.String, issue.ServiceArea);
                    }

                    if (issue.Status > 0)
                    {
                        database.AddInParameter(cmd, "@Status", DbType.String, issue.Status);
                    }

                    if (!string.IsNullOrEmpty(issue.SystemEnvironment))
                    {
                        database.AddInParameter(cmd, "SystemEnvironment", DbType.String, issue.SystemEnvironment);
                    }

                    if (!string.IsNullOrEmpty(issue.UserName))
                    {
                        database.AddInParameter(cmd, "@UserName", DbType.String, issue.UserName);
                    }

                    if (!string.IsNullOrEmpty(issue.UserAccounts))
                    {
                        database.AddInParameter(cmd, "@UserAccounts", DbType.String, issue.UserAccounts);
                    }

                    if (!string.IsNullOrEmpty(issue.GSID))
                    {
                        database.AddInParameter(cmd, "@GSID", DbType.String, issue.GSID);
                    }

                    if (!string.IsNullOrEmpty(issue.ID))
                    {
                        database.AddInParameter(cmd, "@ID", DbType.String, issue.ID);
                    }

                    if (!string.IsNullOrEmpty(issue.PrincipalID))
                    {
                        database.AddInParameter(cmd, "@Principalid", DbType.String, issue.PrincipalID);
                    }

                    if (!string.IsNullOrEmpty(issue.OperatorsId))
                    {
                        database.AddInParameter(cmd, "@operatorsId", DbType.String, issue.OperatorsId);
                    }
                    return database.ExecuteDataSet(cmd).Tables[0];
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + ";" + ex.StackTrace;
                return null;
            }

        }

        private string GetSelectSql(Issue issue, DateTime dtStart, DateTime dtEnd)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select a.issueid,a.exceptionTime,[IssueTitle],a.IssueLevel,c.issuecategoryname as 'IssueCategory',
                        d.issueSourceName as 'IssueSource', e.ClientVersionValue as 'ClientVersion',[CustomersQQ],[CustomersMobile],
                        f.[ServiceAreaName] as [ServiceArea],[Region], 
                        b.BandWidthValue as [BandWidth], h.[SystemEnvironmentValue] as [SystemEnvironment],[DecodingRate],[IsUnblocked]
                        ,[Mark],[Status],[CreateUser],[LastTime],UserName,UserAccounts,Gsid,Id,i.EmployeeName as 'Principalid',j.operatorsName as 'operatorsId'
                        from Issues a
                        join BandWidth b on a.BandWidth = b.BandWidthId
                        join view_GetIssuecategories c on a.[IssueCategory] = c.[issuecategoryid]
                        join IssueSource d on a.[IssueSource] = d.[IssueSourceid]
                        join ClientVersion e on a.ClientVersion = e.ClientVersionId
                        join [ServiceArea] f on a.[ServiceArea] = f.[ServiceAreaID]
                        join [SystemEnvironment] h on a.[SystemEnvironment] = h.[SystemEnvironmentID]
                        join employees i on a.[Principalid]= i.Employeeid
                        join operators j on a.operatorsId = j.operatorsId
                        where 1=1");

            if (dtStart != new DateTime())
            {
                sb.Append(" And [ExceptionTime]>=@dtStart");
            }

            if (dtEnd != new DateTime())
            {
                sb.Append(" And [ExceptionTime] < @dtEnd");
            }

            if (issue.IssueID != Guid.Empty)
            {
                sb.Append(" And [IssueID]=@IssueID");
            }

            if (!string.IsNullOrEmpty(issue.IssueTitle))
            {
                sb.Append(" And [IssueTitle] like '%'+@IssueTitle+'%'");
            }

            if (!string.IsNullOrEmpty(issue.BandWidth))
            {
                sb.Append(" And [BandWidth]=@BandWidth");
            }

            if (!string.IsNullOrEmpty(issue.ClientVersion))
            {
                sb.Append(" And [ClientVersion]=@ClientVersion");
            }

            if (!string.IsNullOrEmpty(issue.CreateUser))
            {
                sb.Append(" And [CreateUser]=@CreateUser");
            }

            if (!string.IsNullOrEmpty(issue.CustomersMobile))
            {
                sb.Append(" And [CustomersMobile]=@CustomersMobile");
            }

            if (!string.IsNullOrEmpty(issue.CustomersQQ))
            {
                sb.Append(" And [CustomersQQ]=@CustomersQQ");
            }

            if (!string.IsNullOrEmpty(issue.DecodingRate))
            {
                sb.Append(" And [DecodingRate]=@DecodingRate");
            }

            if (issue.ExceptionTime != null && issue.ExceptionTime != new DateTime())
            {
                sb.Append(" And [ExceptionTime]>=@ExceptionTime");
            }

            if (!string.IsNullOrEmpty(issue.IssueCategory))
            {
                sb.Append(" And [IssueCategory]=@IssueCategory");
            }

            if (!string.IsNullOrEmpty(issue.IssueLevel))
            {
                sb.Append(" And [IssueLevel]=@IssueLevel");
            }

            if (!string.IsNullOrEmpty(issue.IssueSource))
            {
                sb.Append(" And [IssueSource]=@IssueSource");
            }

            if (!string.IsNullOrEmpty(issue.Region))
            {
                sb.Append(" And [Region]=@Region");
            }

            if (!string.IsNullOrEmpty(issue.ServiceArea))
            {
                sb.Append(" And [ServiceArea]=@ServiceArea");
            }

            if (issue.Status > 0)
            {
                sb.Append(" And [Status]=@Status");
            }

            if (!string.IsNullOrEmpty(issue.SystemEnvironment))
            {
                sb.Append(" And [SystemEnvironment]=@SystemEnvironment");
            }

            if (!string.IsNullOrEmpty(issue.ID))
            {
                sb.Append(" And [ID]=@ID");
            }

            if (!string.IsNullOrEmpty(issue.UserName))
            {
                sb.Append(" And [UserName]=@UserName");
            }

            if (!string.IsNullOrEmpty(issue.UserAccounts))
            {
                sb.Append(" And [UserAccounts]=@UserAccounts");
            }

            if (!string.IsNullOrEmpty(issue.GSID))
            {
                sb.Append(" And [Gsid]=@Gsid");
            }

            if (!string.IsNullOrEmpty(issue.PrincipalID))
            {
                sb.Append(" And [Principalid]=@Principalid");
            }


            if (!string.IsNullOrEmpty(issue.OperatorsId))
            {
                sb.Append(" And a.[OperatorsId]=@operatorsId");
            }

            sb.Append(" order by ID desc");

            return sb.ToString();
        }

        public int GetDayCount(DateTime dt, out string msg)
        {
            int count = 0;

            msg = "";

            const string sql = @"select count(*) from issues where exceptionTime between @StartTime and @EndTime";

            try
            {
                using (var cmd = database.GetSqlStringCommand(sql))
                {
                    database.AddInParameter(cmd, "@StartTime", DbType.DateTime, dt.ToString("yyyy-MM-dd"));
                    database.AddInParameter(cmd, "@EndTime", DbType.DateTime, dt.AddDays(1).ToString("yyyy-MM-dd"));
                    count = Convert.ToInt32(database.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + ";" + ex.StackTrace;
            }
            return count;
        }

        public string GetNewId(string seqName, int length, string currDate, out string msg)
        {
            msg = "";

            const string sql = @"up_common_GetRegularDateVal";

            try
            {
                using (var cmd = database.GetStoredProcCommand(sql))
                {
                    database.AddInParameter(cmd, "seqname", DbType.String, seqName);
                    database.AddInParameter(cmd, "length", DbType.Int32, length);
                    database.AddInParameter(cmd, "currDate", DbType.String, currDate);
                    database.AddOutParameter(cmd, "RegularVal", DbType.String, 50);
                    database.ExecuteNonQuery(cmd);
                    return cmd.Parameters["@RegularVal"].Value.ToString();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + ";" + ex.StackTrace;
                return "";
            }
        }
    }
}
